﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: 
-- Description:	This procedure verifies the requested security permissions and returns
-- either 1 for access granted or 0 for access denied.  It also returns a table of results
-- if the permission requested is "VIEWCHILD"
-- =============================================
CREATE PROCEDURE [dbo].[_Old_VerifySecurity] 
	-- RequestorEntityID - The ID of the entity requesting the access
	-- at the moment this is only the personid, but it could be any entity in the system
	@RequestorEntityID int,
	-- RequestedEntityID - The ID of the entity whose attribute the requestor is asking to modify or
	-- or view
	@RequestedEntityID int,
	-- RequestedPermission - The permission requested.  May be one of four items: VIEW, CHANGE, VIEWCHILDREN, or CHANGECHILDREN 
	@RequestedPermission nvarchar(20),
	-- This determines how far the recursive function can go by adding EntityID2 to the list
	@ChainSoFar nvarchar(4000),
	-- SpecialAttributes - The list of columns that the requestor does not have access to
	@SpecialAttributes nvarchar(1000) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @E1ID int, @E2ID int, @RelationshipName nvarchar(255),@IsRevRelationship bit, 
					   @StartDate datetime, @EndDate datetime, 
					   @SuperRelationship nvarchar(255), @E1CAE2 int, @E1CACE2 int
	DECLARE @ReturnValue int,@RetSpecialAttributes nvarchar(1000),@IsRootCall bit = 0
	DECLARE @ValidJobList varchar(1000), @ThisLevelChainSoFar nvarchar(4000)
	DECLARE @ReturnedEntities table(
		-- AllowedEntityID - The entity ids the Requestor is allowed to read
		AllowedEntityID int,
		-- TopRelationshipName - The relationship name of the connection between RequestorID and the chain that 
		--						 ends at the RequestedID
		TopRelationshipName nvarchar(255),
		-- SpecialAttributes - The comma-separated list of columns that the requestor does not have access to for the specified EntityID
		SpecialAttributes nvarchar(1000)
	);

	IF @ChainSoFar IS NULL OR @ChainSoFar = ''
	BEGIN
		SET @ChainSoFar = ';'
		SET @IsRootCall = 1
		SET @ValidJobList = ''
	END

	DECLARE @MyCursor CURSOR
	SET @MyCursor = CURSOR FAST_FORWARD
	FOR -- This query gets the union of all forward and reverse relationships.  It then filters the 
		-- query based on the comma-separated list of IDs that are passed from the previous
		-- recursive call.  If E2ID is already part of the string, then filter it out of the
		-- security table.
	SELECT * FROM ViewAllowedEntities_ByEntity(@RequestorEntityID,@ChainSoFar)	

	OPEN @MyCursor
	FETCH NEXT FROM @MyCursor
	INTO @E1ID, @E2ID, @RelationshipName, @IsRevRelationship, @StartDate, @EndDate, 
						@SuperRelationship, @E1CAE2, @E1CACE2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- If the row is part of a super-relationship, check the permissions assigned to the 
		-- super-relationship
		IF NOT(@SuperRelationship IS NULL)
			IF @RequestedPermission = 'VIEW'
				SET @E1CAE2 = dbo.ViewAllowedEntities_SuperRelationship(@RelationshipName,@IsRevRelationship,@RequestedPermission)
			ELSE IF @RequestedPermission = 'VIEWCHILDREN'
				SET @E1CACE2 =  dbo.ViewAllowedEntities_SuperRelationship(@RelationshipName,@IsRevRelationship,@RequestedPermission)

		-- E2ID is the ID whose data needs to be viewed or modified.  
		-- If it is the 2nd ID, then check permissions
		IF @E2ID = @RequestedEntityID
		BEGIN
			PRINT @E2ID
			PRINT 'Calculating'
			IF @RequestedPermission = 'VIEW' AND @E1CAE2 > 0
				-- This inserts the current Requested ID into a table by itself and returns that.
				INSERT INTO @ReturnedEntities(AllowedEntityID,SpecialAttributes,TopRelationshipName)
				VALUES(@E2ID,'',@RelationshipName)
			IF @RequestedPermission = 'VIEWCHILDREN' AND @E1CACE2 > 0
				-- This runs a query of the ViewAllowedEntities_By entity table for all related entities
				-- that have a "View" permission given to the next entity up the chain 
				INSERT INTO @ReturnedEntities(AllowedEntityID,SpecialAttributes,TopRelationshipName)
				SELECT E2ID,'',RelationshipName
				FROM ViewAllowedEntities_ByEntity(@E2ID,'') 
				WHERE dbo.ViewAllowedEntities_SuperRelationship(RelationshipName,IsRevRelationship,'VIEW') > 0 AND NOT E2ID = @E2ID
				
			--IF @RequestedPermission = 'CHANGECHILDREN' AND @E1CCCE2 = 1
			--	Return 1
			--IF @RequestedPermission = 'CHANGE' AND @E1CCE2 = 1
			--	Return 1
		END

		ELSE IF @RequestorEntityID = @E2ID
			-- This means the entry is a "SELF" relationship and does not need to be followed
			RETURN
		ELSE
		BEGIN
			-- If the relationship is not directly with RequestedEntityID, then call this function 
			-- passing in E2ID as the new RequestorEntityID if E1CACE2 is greater than 0 (if the
			-- request is to VIEWCHILDREN) or greater than 1 (if the request is to CHANGECHILDREN)
			SET @ThisLevelChainSoFar = @ChainSoFar + cast(@E1ID as varchar(20)) + ';'

			print cast(@E2ID as varchar(20)) + ',' + cast(@RequestedEntityID as varchar(20)) + ',' 
				+ @RequestedPermission + ',' + @thislevelchainsofar

			IF @RequestedPermission = 'VIEW' AND @E1CAE2 > 0
			BEGIN
				INSERT INTO @ReturnedEntities(AllowedEntityID,SpecialAttributes,TopRelationshipName)
				SELECT AllowedEntityID,SpecialAttributes,TopRelationshipName
				FROM ViewAllowedEntities(@E2ID,@RequestedEntityID,@RequestedPermission,@ThisLevelChainSoFar)
			END

			IF @RequestedPermission = 'VIEWCHILDREN' AND @E1CACE2 > 0
			BEGIN
				INSERT INTO @ReturnedEntities(AllowedEntityID,SpecialAttributes,TopRelationshipName)
				SELECT AllowedEntityID,SpecialAttributes,TopRelationshipName
				FROM ViewAllowedEntities(@E2ID,@RequestedEntityID,@RequestedPermission,@ThisLevelChainSoFar)
			END
			--IF @RequestedPermission = 'CHANGE' AND @E1CCCE2 = 1
			--BEGIN
			--	exec @ReturnValue=VerifySecurity @E2ID,@RequestedEntityID,@RequestedPermission,@ChainSoFar,@RetSpecialAttributes OUTPUT
			--END
			--IF @RequestedPermission = 'CHANGECHILDREN' AND @E1CCCE2 = 1
			--BEGIN
			--	exec @ReturnValue=VerifySecurity @E2ID,@RequestedEntityID,@RequestedPermission,@ChainSoFar,@RetSpecialAttributes OUTPUT
			--END
		END

		FETCH NEXT FROM @MyCursor
		INTO @E1ID, @E2ID, @RelationshipName, @IsRevRelationship, @StartDate, @EndDate, 
						   @SuperRelationship, @E1CAE2, @E1CACE2
	END
	CLOSE @MyCursor
	DEALLOCATE @MyCursor

	-- This loops through the table of EntityIDs and determines the attributes that the Requestor is not
	-- allowed to access. The if statement restricts this command to the top level.
	--IF @ChainSoFar = ';'
	--BEGIN
	--	DECLARE @FinalCursor CURSOR
	--	SET @FinalCursor = CURSOR FAST_FORWARD
	--	FOR 
	--		SELECT * FROM @ReturnedEntities ORDER BY TopRelationshipName
		
	--	OPEN @FinalCursor
	--	FETCH NEXT FROM @FinalCursor
	--	INTO @AllowedEntityID, @TopRelationshipName, @SpecialAttributes
	--	WHILE @@FETCH_STATUS = 0
	--	BEGIN

	--		Update @ReturnedEntities 
	--			SET SpecialAttributes=STUFF((SELECT ',' + AttributeName
	--			FROM Entity 
	--				LEFT JOIN DefinedSecurityAttributeAccess DSAA 
	--				ON  (Entity.EntityType = DSAA.EntityType AND (Entity.EntitySubType = DSAA.EntitySubtype OR DSAA.EntitySubtype IS NULL))
	--				LEFT JOIN DefinedAttributes DA ON DSAA.AttributeID = DA.AttributeID
	--			WHERE EntityID = @AllowedEntityID AND AttributeName NOT IN
	--			(SELECT AttributeName
	--			FROM Entity 
	--				LEFT JOIN DefinedSecurityAttributeAccess DSAA 
	--				ON  (Entity.EntityType = DSAA.EntityType AND (Entity.EntitySubType = DSAA.EntitySubtype OR DSAA.EntitySubtype IS NULL))
	--				LEFT JOIN DefinedAttributes DA ON DSAA.AttributeID = DA.AttributeID
	--			WHERE DSAA.RelationshipName = @TopRelationshipName AND EntityID = @AllowedEntityID) FOR XML Path('')),1,1,'')
	--		WHERE TopRelationshipName = @TopRelationshipName AND AllowedEntityID = @AllowedEntityID

	--		FETCH NEXT FROM @FinalCursor
	--		INTO @AllowedEntityID, @TopRelationshipName, @SpecialAttributes
	--	END
	--	CLOSE @FinalCursor
	--	DEALLOCATE @FinalCursor
	--END			


	
	select * from @ReturnedEntities
	return 0
END
